const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔍 分析设备类型映射与实例表的对应关系');
console.log('=========================================');

try {
  // 1. 获取所有实例表（除了已完整的）
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    AND name != 'device_instances_piping_cooling_water_spray_system'
    ORDER BY name
  `).all();
  
  console.log(`📊 需要分析 ${instanceTables.length} 个实例表`);
  
  // 2. 获取所有设备类型映射
  const deviceMappings = db.prepare(`
    SELECT classification_name, specialty, naming_table, position_table, base_params_table
    FROM device_type_mappings
    ORDER BY specialty, classification_name
  `).all();
  
  console.log(`📋 设备类型映射总数: ${deviceMappings.length}`);
  
  // 3. 按专业分组映射
  const mappingsBySpecialty = {};
  deviceMappings.forEach(mapping => {
    if (!mappingsBySpecialty[mapping.specialty]) {
      mappingsBySpecialty[mapping.specialty] = [];
    }
    mappingsBySpecialty[mapping.specialty].push(mapping);
  });
  
  console.log('\\n📋 按专业分组的设备类型:');
  Object.entries(mappingsBySpecialty).forEach(([specialty, mappings]) => {
    console.log(`  ${specialty}: ${mappings.length}个设备类型`);
  });
  
  // 4. 分析每个实例表的映射匹配
  console.log('\\n🔍 实例表与设备映射匹配分析:');
  console.log('='.repeat(80));
  
  const matchResults = [];
  
  for (const table of instanceTables) {
    const tableNameParts = table.name.replace('device_instances_', '').split('_');
    const specialty = tableNameParts[0];
    const deviceKeywords = tableNameParts.slice(1).join('_').toLowerCase();
    
    console.log(`\\n📋 ${table.name}`);
    console.log(`  专业: ${specialty}`);
    console.log(`  设备关键词: ${deviceKeywords}`);
    
    // 查找同专业的映射
    const specialtyMappings = mappingsBySpecialty[specialty] || [];
    
    if (specialtyMappings.length === 0) {
      console.log(`  ❌ 未找到 ${specialty} 专业的设备映射`);
      matchResults.push({
        tableName: table.name,
        specialty: specialty,
        matched: false,
        reason: '专业不匹配'
      });
      continue;
    }
    
    // 尝试精确匹配
    let bestMatch = null;
    let matchScore = 0;
    
    for (const mapping of specialtyMappings) {
      const mappingKeywords = mapping.classification_name.toLowerCase();
      
      // 简单的关键词匹配评分
      let score = 0;
      
      // 检查设备名称包含的关键词
      const deviceWords = deviceKeywords.split('_');
      const mappingWords = mappingKeywords.split(/[\\s，、]/);
      
      for (const deviceWord of deviceWords) {
        for (const mappingWord of mappingWords) {
          if (deviceWord.includes(mappingWord) || mappingWord.includes(deviceWord)) {
            score += 1;
          }
        }
      }
      
      if (score > matchScore) {
        matchScore = score;
        bestMatch = mapping;
      }
    }
    
    if (bestMatch) {
      console.log(`  ✅ 最佳匹配: ${bestMatch.classification_name} (评分: ${matchScore})`);
      console.log(`     命名表: ${bestMatch.naming_table}`);
      console.log(`     位置表: ${bestMatch.position_table}`);
      console.log(`     基础参数表: ${bestMatch.base_params_table}`);
      
      matchResults.push({
        tableName: table.name,
        specialty: specialty,
        matched: true,
        bestMatch: bestMatch,
        matchScore: matchScore
      });
    } else {
      console.log(`  ⚠️  无精确匹配，使用专业默认映射: ${specialtyMappings[0].classification_name}`);
      
      matchResults.push({
        tableName: table.name,
        specialty: specialty,
        matched: 'default',
        bestMatch: specialtyMappings[0],
        matchScore: 0
      });
    }
  }
  
  // 5. 生成匹配统计
  console.log('\\n📊 匹配结果统计');
  console.log('========================');
  
  const exactMatches = matchResults.filter(r => r.matched === true).length;
  const defaultMatches = matchResults.filter(r => r.matched === 'default').length;
  const noMatches = matchResults.filter(r => r.matched === false).length;
  
  console.log(`✅ 精确匹配: ${exactMatches}个`);
  console.log(`⚠️  默认匹配: ${defaultMatches}个`);
  console.log(`❌ 无法匹配: ${noMatches}个`);
  
  // 6. 分析属性表使用情况
  console.log('\\n📋 属性表使用分析');
  console.log('========================');
  
  const tableUsage = {};
  
  matchResults.forEach(result => {
    if (result.bestMatch) {
      const { naming_table, position_table, base_params_table } = result.bestMatch;
      
      if (!tableUsage[naming_table]) tableUsage[naming_table] = { type: 'naming', count: 0 };
      if (!tableUsage[position_table]) tableUsage[position_table] = { type: 'position', count: 0 };
      if (!tableUsage[base_params_table]) tableUsage[base_params_table] = { type: 'base_params', count: 0 };
      
      tableUsage[naming_table].count++;
      tableUsage[position_table].count++;
      tableUsage[base_params_table].count++;
    }
  });
  
  console.log('属性表使用频率:');
  Object.entries(tableUsage).forEach(([tableName, info]) => {
    console.log(`  ${tableName} [${info.type}]: ${info.count}次`);
  });
  
  // 7. 验证属性表存在性
  console.log('\\n🔍 验证属性表存在性');
  console.log('========================');
  
  const uniqueTables = [...new Set(Object.keys(tableUsage))];
  
  for (const tableName of uniqueTables) {
    try {
      const tableExists = db.prepare(`
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name = ?
      `).get(tableName);
      
      if (tableExists) {
        const fieldCount = db.prepare(`PRAGMA table_info(${tableName})`).all()
          .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name)).length;
        console.log(`  ✅ ${tableName}: ${fieldCount}个字段`);
      } else {
        console.log(`  ❌ ${tableName}: 不存在`);
      }
    } catch (error) {
      console.log(`  ❌ ${tableName}: 检查失败 - ${error.message}`);
    }
  }
  
  console.log('\\n🎯 建议的更新策略:');
  console.log('========================');
  console.log('1. 使用标准三表组合: table_c2 (命名) + table_c4/c6 (位置) + 专业特定基础参数表');
  console.log('2. 对于无法精确匹配的设备，使用该专业的第一个映射作为默认');
  console.log('3. 确保所有使用的属性表都存在且结构完整');
  console.log('4. 备份原表数据以防更新失败');
  
} catch (error) {
  console.error('❌ 分析失败:', error.message);
} finally {
  db.close();
}